<?xml version="1.0" encoding="UTF-8"?>
<rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0"><channel><title><![CDATA[MSSQL2014 - evan.ly]]></title><description><![CDATA[A lo-fi collection of things I've ever encountered as a IT Management Consultant / Business Analyst / Human-shaped IT Swiss-Army Knife.]]></description><link>http://evan.ly/</link><generator>Ghost 0.5</generator><lastBuildDate>Tue, 26 May 2026 16:37:34 GMT</lastBuildDate><atom:link href="http://evan.ly/tag/mssql2014/rss/" rel="self" type="application/rss+xml"/><ttl>60</ttl><item><title><![CDATA[Move SQL Server system DBs (master and msdb) to a new location]]></title><description><![CDATA[<p>How to move the <code>master</code> and <code>msdb</code> databases ands their logfiles to a new location. I used <code>L:\</code> as my log disk, and <code>E:\</code> as my database disk.</p>

<pre><code>USE master  
Go  
ALTER DATABASE model  
    MODIFY FILE ( NAME = 'modeldev' , FILENAME = E:\MSSQL\DATA\model.mdf' );
Go  
ALTER DATABASE model  
    MODIFY FILE ( NAME = 'modellog' , FILENAME = L:\MSSQL\DATA\modellog.ldf' );
Go  
ALTER DATABASE msdb  
    MODIFY FILE ( NAME = 'MSDBData' , FILENAME = 'E:\MSSQL\DATA\MSDBData.mdf' );
Go  
ALTER DATABASE msdb  
    MODIFY FILE ( NAME = 'MSDBLog' , FILENAME = 'L:\MSSQL\DATA\MSDBLog.ldf' );
Go  
</code></pre>

<p>See where your system dbs are located:  </p>

<pre><code>USE master  
Go  
SELECT  
    DB_NAME(database_id)  AS "Database Name"
  , name                  AS "Logical File Name"
  , physical_name         AS "Physical File Location"
  , state_desc            AS "State"
FROM  
    sys.master_files
WHERE  
    database_id IN (DB_ID(N'msdb'), DB_ID(N'model'), DB_ID(N'temp'))
ORDER BY  
    DB_NAME(database_id);
Go  
</code></pre>]]></description><link>http://evan.ly/move-sql-server-system-dbs/</link><guid isPermaLink="false">5184e02e-a6f5-4282-bd59-38be2f90917c</guid><category><![CDATA[MSSQL]]></category><category><![CDATA[SQL Server]]></category><category><![CDATA[MSSQL2014]]></category><dc:creator><![CDATA[Evan]]></dc:creator><pubDate>Fri, 24 Oct 2014 16:00:29 GMT</pubDate></item><item><title><![CDATA[Shrink SQL Database Transaction Log]]></title><description><![CDATA[<p>I was migrating a large-ish (~1GB) database from one server (MS SQL Server 2008) to another (MS SQL Server 2014), with a full transaction log. The transaction log uncompressed and restored was ~230GB.</p>

<p>As this was a new server, with a new set of backup and disaster recovery procedures, I decided to do a full backup, and then completely truncate the transaction log.   </p>

<p><strong>Your Transaction Log could be crucially important in a recovery scenario, do not just do this because it's big and you want it to be small - it has a purpose.</strong></p>

<h6 id="mssqlserver20142008r2">MS SQL Server 2014 (2008/R2+):</h6>

<pre><code>ALTER DATABASE &lt;DatabaseName&gt; set recovery simple
GO
CHECKPOINT
GO
DBCC SHRINKFILE (&lt;TransactionLog&gt;,1)
GO
ALTER DATABASE &lt;DatabaseName&gt; set recovery full
GO   
</code></pre>

<h6 id="mssqlserver2005">MS SQL Server 2005:</h6>

<pre><code>USE &lt;DatabaseName&gt;
GO
DBCC SHRINKFILE (&lt;TransactionLog&gt;, 1)
BACKUP LOG &lt;DatabaseName&gt; WITH TRUNCATE_ONLY
DBCC SHRINKFILE(&lt;TransactionLog&gt;, 1)
GO
</code></pre>

<p>Query: <a href="https://support.myeasyprojects.net/kb/a163/transaction-log-is-full_-how-do-i-shrink-it.aspx">Source</a> <br>
GUI: <a href="http://technet.microsoft.com/en-us/library/ms190757(v=sql.105).aspx">Source</a></p>]]></description><link>http://evan.ly/shrink-transaction-log/</link><guid isPermaLink="false">8ce560a8-ee18-414d-9183-9935120bd63b</guid><category><![CDATA[MSSQL]]></category><category><![CDATA[SQL Server]]></category><category><![CDATA[MSSQL2014]]></category><dc:creator><![CDATA[Evan]]></dc:creator><pubDate>Sat, 11 Oct 2014 05:01:06 GMT</pubDate></item><item><title><![CDATA[MS SQL Database Stuck Restoring]]></title><description><![CDATA[<p>When an MS SQL Server database is being restored, and either errors out or times out, and the staus of "Restoring" persists, even after a restart and a clear failure of the Restore operation, use the following to ge things back up, quick 'n dirty style.</p>

<ol>
<li><p>Stop MSSQLSERVER and related services.</p></li>
<li><p>Open your DATA folder, or wherever the .LDf and .MDf files are  stored (of the DB being restored).</p></li>
<li><p>Move the files to another folder</p></li>
<li><p>Restart SQLSERVER service (and related services).</p></li>
<li><p>Start my MS SQL Management Studio and delete the database in question.</p></li>
<li><p>Make sure the *.LDF and *.MDF files are gone from their original location folder.</p></li>
<li><p>A. Delete the database in question and attempt the restore from scratch; OR: <br>
B. Take the database Offline, copy the .LDF and .MDF files into the DATA directory (from Step 3).</p></li>
<li><p>Bring database Online.</p></li>
</ol>

<p><a href="http://social.msdn.microsoft.com/Forums/sqlserver/en-US/8dd1b91d-3e14-4486-abe6-e3a550bfe457/database-in-restoring-state-help?forum=sqldatabaseengine">Source</a>
<a href="http://stackoverflow.com/questions/520967/sql-server-database-stuck-in-restoring-state">Source</a></p>]]></description><link>http://evan.ly/ms-sql-database-stuck-restoring/</link><guid isPermaLink="false">90affec0-5c51-4ee2-ae3c-12fa6bea1dd6</guid><category><![CDATA[MSSQL]]></category><category><![CDATA[SQL Server]]></category><category><![CDATA[MSSQL2014]]></category><dc:creator><![CDATA[Evan]]></dc:creator><pubDate>Sat, 11 Oct 2014 04:53:36 GMT</pubDate></item></channel></rss>