I’ve wrote this script to get data file allocated and used space. In addition to this I’ve also added auto growth setting details for the data file. Written two scripts for fetching the details, first one will get you the details for one database and second one will give you the details for all the database data files.
SQL Server 2012 codenamed SQL Server Denali is on the way to market by 2012. As you all know SQL Server 2012 has new features related to security, we have contained database where you are allowed to create contained users and the next feature is server level roles, other security features are not related to this article, we will cover those later. I wrote some scripts earlier to retrieve permissions from SQL 2000 and SQL 2005 (links below) however these scripts are no longer useful for SQL Server 2012 as it won’t return the new contained user and server level role permissions.
I’ve written a startup procedure to check SQL Server status when the SQL Server gets restarted. Most of the time as a DBA when a Server gets rebooted or SQL Services is restarted we need to make sure everything is fine in SQL server to make sure SQL server is running healthy. Some companies will have budget to buy a monitoring tool, so that all these things will be taken care by that tool. This small procedure will help the DBA’s to know the status of SQL Server in HTML format through mail when it’s restarted.
Almost every application DBA would have a need to document the various database objects such as tables, views, stored procedures and functions. I wasn’t an exception and exploring ways to document the 400+ tables we have. Basically, this would serve the following purpose:
Use this VB script to check SQL Server connectivity. You can add N number of SQL Server instances to verify its status.
RA
| #
Thanks for the script. I had database with space between the names.
I modified to use square brackets:
FROM [?].sys.database_principals a
LEFT OUTER JOIN [?].sys.database_role_members
and
from [?].sys.database_permissions join [?].sys.sysusers U
on grantee_principal_id = uid join [?].sys.sysobjects
Gangadhar NG
| #
This tip is helpful enough for me.
thanks.
Zukunftsmusik
| #
Thanks! That helped my a lot. But there’s a little mistake:
SQL_Latin1_General_CP1_CI_AS stands for “Latin1-General, case-insensitive, accent-sensitive [...]” therefore sort order 52.
If you want to set the sort order to 54 the correct collation would be “SQL_Latin1_General_CP1_CI_AI”
Amit Bhatt
| #
Hi Deepak,
Thanks for such a nice article.
You missed one thing to add in code:
@article = ‘all’,
Hence the script will be like this:
EXEC sp_addsubscription
@publication = ‘mypublication’,
@article = ‘ALL’,
@subscriber = ‘Subscriberservername’,
@destination_db = ‘mydestinationdbname’,
@reserved=’Internal’
Error 18486 | Platformblog
| #
[...] SQL-Articles » Troubleshooting Login failed Error 18456This is one of the infamous error message (and number) that most of the DBAs …. 18486. Login failed for user ‘%.*ls’ because the account is currently locked out. [...]