MSSQL Tips: Tip #33 – SQL Server Master Data Services (MDS) custom Delete Stored Procedure

Featured

By default entity members in Master Data Services (MDS) are not deleted permanently. They are “soft” deleted (deactivated) and available for recovery from a transaction history.

In this tip we have listed several methods for deleting MDS members and we have provided a script for the custom “delete” stored procedure.

Please read my latest MSSQL Tip: “SQL Server Master Data Services (MDS) custom Delete Stored Procedure“.

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

Share

MSSQL Tips: Tip #32 – Resolving SQL Server Master Data Services (MDS) Patching Errors

Featured

When you patch a server hosting the MDS Web Application with an SQL Server update and if you have not completed the upgrade sequence users may get this error in the MDS Web Application on client side.

Check out details in the following MSSQL Tip: “Resolving SQL Server Master Data Services (MDS) Patching Errors“.

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

Share

MSSQL Tips: Tip #29 – SQL Server Database Decommissioning Check List

Featured

Different companies have various requirements and processes for database or database server decommissioning. In this tip we have provided steps that will help you decommission a database and make sure you have a good back-out plan (for example, in case there is a need to bring the database back online). We have also provided scripts that will help you identify dependencies in case there is a need to migrate the database instead of decommissioning.

You may already have a process documented, but this list will help you review most of the dependencies and make sure you are prepared for either a migration or decommissioning.

Please read my latest MSSQL Tip: “SQL Server Database Decommissioning Check List“.

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

Share

MSSQL Tips: Tip #27 – SQL Server Master Data Services (MDS) Database Restore Steps

Featured

In some cases when you try to restore a Master Dats Services database you can get errors. To complete the restore you may need to perform additional steps.

Please read my latest MSSQL Tip: “SQL Server Master Data Services (MDS) Database Restore Steps“.

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

Share

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 #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

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

MSSQL Tips: Tip #17 – Using Policy Based Management to check that SQL Server indexes are created in the correct filegroup

Featured

Another tip about using Policy Based Management for the SQL Server checks.

Please read the latest MSSQL Tip: “Using Policy Based Management to check that SQL Server indexes are created in the correct filegroup“.

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

Share

MSSQL Tips: Tip #14 – Using WMI alerts to import SQL Server Default Trace events

And another tip about WMI alerts use.

Please read the latest MSSQL Tip: “Using WMI alerts to import SQL Server Default Trace events“.

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

Share

Q&A: Script for the Job that will shrink the log files for all databases

There was a user question on how to shrink the log files with dynamic SQL (without hard coding databases names). The script is provided below. But first – make sure that you size the log files properly (large enough to grow) and make sure that you backup regularly the log files (or use simple recovery mode). And do not truncate the logs, shrink them just to the size you need. So, if my log files do not usually grow larger than 4 GB I will use the following script:

SET NOCOUNT ON
CREATE table #tran_log_space_usage(
database_name sysname
, log_size_mb float
, log_space_used float
, status int
);
insert into #tran_log_space_usage
exec(‘DBCC SQLPERF ( LOGSPACE )’) ;

delete from #tran_log_space_usage where database_name = ‘Exclude_DB’

EXEC dbo.sp_msforeachdb ‘
DECLARE @log_file VARCHAR(150)
IF (SELECT DB_ID(”?”)) > 4 and (SELECT DATABASEPROPERTY(”?”, ”IsOffline”)) <>1
BEGIN
PRINT ”?”
IF (select log_size_mb as LogSizeMB
from #tran_log_space_usage
where database_name = ”?”) > 4048
BEGIN
select @log_file = name
from [?].sys.database_files where file_id = 2
USE [?]
DBCC SHRINKFILE (@log_file , 4048)
END
PRINT ”/*********************************/”
END’

DROP table #tran_log_space_usage

You can add exceptions for the databases as well (see ‘Exclude_DB’ in script).

Share