Shrink database files and DBCC CheckDB

Shrinking database files is never a good idea. Read this article about alternative method on removing free space from the files: http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/.

In rare occasion when you decide to do this make sure you run DBCC CHECKDB after shrinking the file. You may encounter allocation errors:

CHECKDB found 1 allocation errors and 0 consistency errors in database…

Some of these errors could be fixed by running table or index rebuild command:

ALTER TABLE dbo.Test_1 REBUILD;

 

Share

MSSQL Tips: Tip #26 – Export Data from IBM DB2 iSeries to SQL Server

Featured

Steps to install a data source driver and to configure an ODBC connection to the IBM DB2 for i (iSeries). Also an example of SSIS (SQL Server Integration Services) package that exports data from the iSeries to a SQL Server database.

Please read my latest MSSQL Tip: “Export Data from IBM DB2 iSeries to SQL Server“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

Share

MSSQL Tips: Tip #24 – SQL Server 2016 Query Store Queries

Featured

What are some practical applications of using the Query Store? What queries can be run against the Query Store? What sort of questions can I get answered?

Please read my latest MSSQL Tip: “SQL Server 2016 Query Store Queries“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

Share

MSSQL Tips: Tip #23 – SQL Server 2016 Query Store Example

New tip with example on how to use SQL Server 2016 Query Store, how to track multiple execution plans and how to force an efficient plan.

Please read the latest MSSQL Tip: “SQL Server 2016 Query Store Example“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

Share

MSSQL Tips: Tip #22 – Compare SQL permissions using SQL Server Data Tools

Featured

In this new tip you will find out how to use Visual Studio and SQL Server Data Tools to compare a database permissions and to copy permissions (including users and logins).

Please read the latest MSSQL Tip: “Compare SQL permissions using SQL Server Data Tools“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

Share

MSSQL Tips: Tip #21 – SQL Server 2016 Query Store Introduction

Featured

As promised here is the first tip about SQL Server 2016 Query Store.

Please read the MSSQL Tip here: “SQL Server 2016 Query Store Introduction“. Stay tuned for more tips about the Query Store.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/

Share

SQL Server 2016 CTP 2.2 is now available

SQL Server 2016 CTP 2.1 became available on June 24th 2015 about one month after Microsoft made public SQL Server 2016 CTP 2.0.

Today CTP 2.2 is available for download. Among all of the new Database Engine features Query Store stands out as a great addition to the performance tuning tools currently available out of the box.

Stay tuned for the MSSQLTips articles about SQL Server 2016 Query Store.

Share

MSSQL Tips: Tip #20 – Monitor SQL Server Master Database Changes with WMI Alerts

In this new tip you will find out how to setup monitoring of the master database and get real time notifications when the new objects created there.

Please read the latest MSSQL Tip: “Monitor SQL Server Master Database Changes with WMI Alerts“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

Share

MSDB log file max size reset after SQL Patches

I have encountered this several times already after different Cumulative Updates/Service Packs…

We set fixed maximum size for SQL Server databases and update the max size when required. But after installing Service Pack and Cumulative Updates the maximum size is reset to 2,097,152 MB for the MSDB log file:
max size - msdb log

I am going to submit this issue to the Microsoft Connect (hopefully it still works and active) or check if somebody else had similar issues. As this happens only with one system database and only with one file I would suspect that this is a bug.

This is just something to watch out and remember to review after SQL Server patching if you use fixed maximum files sizes.

Share