次の方法で共有

Shrinking SQL Server Databases

  • [アーティクル]

I was having difficulty shrinking my VSIP SQL Server 2005 Databases to remove unnecessary transactions and empty space from the log file. It took me a while to solve so I thought I should post my results here.  I got the answer from  https://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/  and here is the resulting script (which I have since put in a stored proc - but I will not include that detail here):

Note that you will need your database name **and** the SQL Server name of your log file (different to the NTFS name) for this look at  sys.database_files. In this script my database name is "MyDatabaseName" and the SQL Server name of my log file is "MyDatabaseName_log" :

          USE MyDatabaseName

          DBCC SHRINKFILE(MyDatabaseName_log, 1);

          BACKUP LOG MyDatabaseName WITH TRUNCATE_ONLY

          DBCC SHRINKFILE(MyDatabaseName_log, 1);

DBCC SHRINKDATABASE(MyDatabaseName) -- Optional - not sure how much this helps

With this done I got back 50% of the entire space on my disk - guess I needed it :-) !! 

Please read https://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/ for more info - particularly if your log file contains data that is important to you.

Comments