Shirking sharepoint database

We had an issue the other day when one of the Sharepoint database logs became extremely large as the server where it was installed didn’t have SQLServer Management Studio so using “sqlcmd” we were able connect to the database and shrink the logfile and it ran on SQLServer lite Edition.

Here are the steps:
1) Connect to the sharepoint database via sqlcmd
C:> sqlcmd -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

2) List the databases
1> select name, database_id from sys.databases;
2> go
name database_id
—————————————————————————
master 1
tempdb 2
model 3
msdb 4
SharePoint_Config_06940255-49ff-4ff9-87b9-fd2e0841f25b 5
SharePoint_AdminContent_b002cd77-a299-4ed7-998c-2fec58619fa5 6
WSS_Search_CALSHP1AP01 7
WSS_Content 8

3) Connect to the database that had the large logfile
1> use [SharePoint_Config_06940255-49ff-4ff9-87b9-fd2e0841f25b]
2> go

3) Get the current size of the files
1> select name, size from sys.sysfiles;
2> go
name size
——————————————————— —————————-
SharePoint_Config_06940255-49ff-4ff9-87b9-fd2e0841f25b 664
SharePoint_Config_06940255-49ff-4ff9-87b9-fd2e0841f25b_log 2171808
(2 rows affected)

4) Shrink the logfile
1> dbcc shrinkfile(‘SharePoint_Config_06940255-49ff-4ff9-87b9-fd2e0841f25b_log’, 2)
2> go
Cannot shrink log file 2 (SharePoint_Config_06940255-49ff-4ff9-87b9-fd2e0841f25b_log) because all logical log files are in use.

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
—— ———– ———– ———– ———– ————–
5 2 1986704 63 1986704 56
(1 rows affected)
DBCC execution completed. If DBCC printed error messages, contact your system ad ministrator.

1> BACKUP LOG [SharePoint_Config_06940255-49ff-4ff9-87b9-fd2e0841f25b] with truncate_only
2> go
1> dbcc shrinkfile(‘SharePoint_Config_06940255-49ff-4ff9-87b9-fd2e0841f25b_log’, 2)
2> go

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
—— ———– ———– ———– ———– ————–
5 2 256 63 256 56
(1 rows affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

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