Help with contents of a script
-
Hi all, im the first to admit im rather useless when it comes to SQL (at the moment)
I've contacted the 3rd party that is responsible for the database about our growing transaction logs, who have given me a script which shrinks the logs. Before I put this in, im curious as to what it actually does. I know I could contact the third party but it would probably take around 2 weeks to get an answer from anyone. Ive managed to reverse engineer parts of it but other parts im pretty stuck with. If anyone could let me know if im on the right lines with what i've put id be greatfull. The scrit is below, ive put REM next to the parts that ive managed to figure out:
-----------------------------------------------------------------------------------------------------
@echo off
REM checks for servername\instance (%1) and database name (%2) has been included in the syntax
if .%1==. goto DisplayUsage
if .%2==. goto DisplayUsage
REM Stores the below commands into a file called shrink.sql in the temp folder
echo shrinking log file for database %2
echo.
echo declare @sql nvarchar(255), @fn sysname > "%temp%\shrink.sql"
echo set @sql = 'alter database [%2] set recovery simple' >> "%temp%\shrink.sql"
echo exec sp_executesql @sql >> "%temp%\shrink.sql"
echo select @fn = name from sys.database_files where type = 1 >> "%temp%\shrink.sql"
echo dbcc shrinkfile(@fn,1) >> "%temp%\shrink.sql"
echo. >> "%temp%\shrink.sql"
echo.
REM -S sets SQL Server Name and Instance to what is stored in %1
REM -d sets the database name to what is stored in %2
REM -U sets username to SA
REM -P Sets Password to ?S1M5455796?
REM -n Removes numbering and the prompt symbol (>) from input lines
REM -i sets input file to "%temp\shrink.sql" which contains the commands
osql -S %1 -d %2 -Uusername -Ppassword -n -i"%temp%\shrink.sql"
echo.
goto Exit
:DisplayUsage
echo Shink the log file of a SQL Database v2.00
echo.
echo Usage: ShrinkDBLog {Server}\{Instance} {DatabaseName}
echo.
echo Where:
echo Server\Instance is the SQL Server and the SQL Server Instance if one is present
echo DatabaseName SQL database name to shrink, eg sims
echo.
echo Examples:
echo ShrinkDBLog AdminPC\SIMS sims
echo ShrinkDBLog AnotherPC sims
------------------------------------------------------------------------------------------------------
This is the part where im not really sure whats going on? I know it stores the commands in shrink.sql but im not sure what exactly the commands do? Ive managed to figure out TYPE1 = Log files?
declare @sql nvarchar(255), @fn sysname > "%temp%\shrink.sql"
set @sql = 'alter database [%2] set recovery simple' >> "%temp%\shrink.sql"
exec sp_executesql @sql >> "%temp%\shrink.sql"
select @fn = name from sys.database_files where type = 1 >> "%temp%\shrink.sql"
dbcc shrinkfile(@fn,1) >> "%temp%\shrink.sql"
echo. >> "%temp%\shrink.sql"
-
Pretty much got it worked out more of less now ive had time to sit down and have a look at it properly.
-
it's effectively a script which performs the same operations as this GUI menu "Shrink Files" in SSMS performs...the followup screen lets you select either the database file(s) or the log file(s)
no besides that, know this:
shrinking is bad. this is a DBA operation that you would /should only do under rare circumstances, like doing a HUGE Extract-Transform-Load , into staging tables, and then deleting the staging tables when it's complete.
Shrinking increases fragmentation , which can severely impact performance, and SQL server will undoubtedly grow again right back tot eh same size it needed before.
There's al ot of bad advice out there that suggest shrinking a log file, when actually better log management by performing regular log backups after your full backup is what is actually required.
That's probably a hihg percentage of some of the forum posts here... the old "help, my log file grew out of control and shut down my server because I'm out of disk space" kind of issues.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply