Majority of the examples I could find online to add database filegroups and/or files using PowerShell were for new databases. They made use of methods that didn’t work for what I was after. What I needed to do was add a database filegroup and file to an existing database. Examples, and until recently, documentation, I
Continue reading Add a database filegroup and file to an existing database using PowerShell
SQLjobvis is a tool I’ve blogged about before. For more info on what SQLjobvis is and how it can help you you can read my blog post here. A reader had commented on that post and mentioned that they didn’t have the necessary permissions to run SQLjobvis. But what are those permissions? The website doesn’t appear to
Continue reading Permissions required for SQLjobvis
Who is Active? Not I! 3 blog posts in 2014, all published in the beginning of the year. However in 2015 I’ll be looking to change all that. Back to SQL! Who is Active, an awesome stored procedure I use on a daily basis to see what is going on, at that very moment, on
Continue reading What I Use – sp_WhoIsActive
In order to execute queries under a different security context (such as another login) using the EXECUTE AS clause, you must be able to impersonate the user. To grant impersonate permissions on a specific login, run the query below. To test this works you can run the query below.
A user will usually receive this error message when trying to execute xp_cmdshell and does not have sufficient permissions. To grant permissions to execute xp_cmdshell using TSQL, run the query below. To test this works I usually run the query below. To test the granted execute permissions work for the intended user, I impersonate them
Continue reading The EXECUTE permission was denied on the object ‘xp_cmdshell’, database ‘mssqlsystemresource’, schema ‘sys’
To enable line numbers in SQL Server Management Studio, click on the Tools menu and Options. In the left pane of the Options box, expand Text Editor and click on All Languages. On the right, under Display, you’ll see a check box for Line numbers. Check the check box to enable line numbers. I have
Continue reading How to enable line numbers in SSMS
I noticed the following message in the SQL Server ErrorLog for one of the database servers which is in a database mirroring setup (currently the mirror). Error: 1438, Severity: 16, State: 2 The server instance Witness rejected configure request; read its error log file for more information. The reason 1427, and state 31, can be
Continue reading The server instance Witness rejected configure request
Today I was working on some PowerShell scripts where I ran into the following error message: Invoke-Sqlcmd : Object reference not set to an instance of an object. Below is the PowerShell code which was throwing the error. I discovered the culprit was the $Query parameter. The $Query parameter contents contained a TSQL query to
Continue reading Invoke-Sqlcmd : Object reference not set to an instance of an object.
Before installing SQL Server 2008 R2 on Windows Server 2008 R2 SP1, the .NET Framework Feature must be added. To do this via PowerShell run the following: The Out-Null is optional but by piping the output means nothing will get returned to the PowerShell window.
While trying to execute the below TSQL query I got the following error message: SERVERNAME(User): Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type int. I gathered that I was receiving this error message because the SUM of the SQL Server database files was larger than the maximum
Continue reading Arithmetic overflow error converting expression to data type int.