🗄️ SQL-Server
(47 posts)
T-SQL queries, performance tuning, security management, and administration guides for Microsoft SQL Server.
📅 2025-01-05
✍️ Bart Schelstraete
Below is an example script which adds a Windows domain group to each database, with the db_reader role. DECLARE @DBName NVARCHAR(255); DECLARE @SQL NVARCHAR(MAX); DECLARE @GroupName NVARCHAR(255) =…
Read more →
📅 2024-03-08
✍️ Bart Schelstraete
You may use the T-SQL code below to find the last logon dates for the SQL accounts. use master; set nocount on -- find users, last logon time, and disabled state declare @users_login_time table (…
Read more →
📅 2024-03-08
✍️ Bart Schelstraete
The T-SQL query below will list the users configured within the database, and the permissions which these users have. SELECT [UserType] = CASE princ.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN…
Read more →
📅 2023-02-17
✍️ Bart Schelstraete
To identify the queries that are responsible for high CPU activity currently, run the following statement: SELECT TOP 10 s.session_id, r.status, r.cpu_time, r.logical_reads, r.reads, r.writes,…
Read more →
📅 2023-02-14
✍️ Bart Schelstraete
Well, SQL statements can use a lot of log space, and you most probably want to find out which SQL statement is using all of your log space. The T-SQL code below will show you the SQL statements using…
Read more →
📅 2022-12-28
✍️ Bart Schelstraete
First off, you should never shrink a database file unless you know it won't need the space back again. (There are some caveats to that statement, but it mostly holds true.) If that is the space the…
Read more →
📅 2022-12-14
✍️ Bart Schelstraete
After a while, the msdb database can be overloaded with backup history, and clearing that history can be a pain. You can use the T-SQL code below to clear the msdb backup history in incremental steps…
Read more →
📅 2022-01-06
✍️ Bart Schelstraete
You can group datetime fields by year/month by using a simple 'format'. Of course, there are other possibilities for doing this. See the example below: declare @format varchar(100) = 'yyyy/MM' SELECT…
Read more →
📅 2022-01-06
✍️ Bart Schelstraete
When SQL Server needs a database master key to decrypt or encrypt a key, SQL Server tries to decrypt the database master key with the service master key of the instance. If the decryption fails, SQL…
Read more →
📅 2022-01-02
✍️ Bart Schelstraete
It is possible that SQL jobs are "hanging" in a suspended state. The SQL statements below will give you an indication of what the job is waiting on. The first SQL statement will create a small stored…
Read more →
