Collection of useful Commands for SQL Server DBA


In this article I have tried to give as much as I can from my real time experience. You may try these commands on one of your test/pre-production server.

T-SQL Used to shrink the transaction log
Not all commands are necessary, some commands are used to fix error countered.

BACKUP LOG TestDBTO DISK = N’C:TestDB_Adhoc_TransLog.Bak’;GO — Get The file NameEXEC sp_helpdb ‘TESTDB’GO USE TestDbGO– SHRINK it to 10MBDBCC SHRINKFILE (‘TestDb_log’,TRUNCATEONLY);GO USE masterGOALTER DATABASE TestDb SET OFFLINE WITHROLLBACK IMMEDIATEGOALTER DATABASE TestDb SET ONLINEGO USE TestDbGO– SHRINK it to 10MBDBCC SHRINKFILE (‘TestDb_log’,TRUNCATEONLY);GO /*Cannot shrink log file 2 (TestDb_log) because the logical log file located at the end of the file is in use. (1 row(s) affected)*/ USE [master]GOALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAITGO USE TestDbGO– SHRINK it to 10MBDBCC SHRINKFILE (‘TestDb_log’,TRUNCATEONLY);GO /*DbId USE [master]GOALTER DATABASE [TestDb] SET RECOVERY FULL WITH NO_WAITGO ALTER DATABASE TestDb MODIFY FILE ( NAME = ‘TestDb_log’, SIZE = 10);GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: