This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Does SQL Server reuses space to accommodate new data, When we delete variable length column or rows?
Whenever rows or columns are deleted from a table, SQL Server reuses space to accommodate new data.
However, this is not the case when a variable length column is deleted.
The space used by this type of column is not reclaimed automatically.
Variable length columns can be of data types varchar, nvarchar, varchar(max), nvarchar(max), varbinary, varbinary(max), text, ntext, image, sql_variant, and xml.
As a DBA, what should be your daily checklist for Database Server?
- Check status of backups
- Review the nightly run processes
- Check the error log files
- Check the windows error log files
- Check the status of SQL Agent jobs
- Review the performance related report of SQL Server
- Check the storage information
- Review the configured Service broker
- Indentify the different type of fragmentations
How to recycle SQL Server Agent Error logs?
We can use sp_cycle_agent_errorlog system procedure to recycle SQL Server Agent Error logs.
When we cycle the error logs it is easier to open up a SQL Server Agent Error Log file when it is smaller in size.
What is a Scheduled Jobs or What is a Scheduled Tasks?
Scheduled tasks let user automate processes that run on regular or predictable cycles. User can schedule administrative tasks, such as cube processing, to run during times of slow business activity.
User can also determine the order in which tasks run by creating job steps within a SQL Server Agent job. E.g. back up database, Update Stats of Tables. Job steps give user control over flow of execution. If one job fails, user can configure SQL Server Agent to continue to run the remaining tasks or to stop execution.
What should be your recommendations for Shrinking a database?
Shrink the file by using Truncate Only: First it removes the inactive part of the log and then perform shrink operation.
Rebuild / Reorganize the indexes once the shrink is done so the Fragmentation level is decreased.
What are the different type of log files?
- SQL Server Error Log
- SQL Server Agent Log
- Windows Event Log
- SQL Server Setup Log
- SQL Server Profiler Log
How many datafiles I can put in Tempdb? What is the effect of adding multiple data files?
The most effective configuration is to set tempdb on its own separate fast drive away from the user databases. I would set the number of files based on # of cpu’s divided by 2.
Which trace flag is used to find log record for SQL Server connection?
trace flag number: 4013
What do you know about the resource database?
All sys objects are physically stored in resource database and logically available on every database.
Resource database can faster the service packs or upgrades.
Why can’t I view the list of databases, tables, views in the SQL ServerManagement Studio Object Explorer?
This issue is due to not having VIEW DEFINITION permission on the specific object.
To resolve this issue, you need to grant VIEW DEFINITION permission on the object to the user which is mapped to the login you used.
You can perform this by running the following Transact-SQL.
Can we initiate a external COM object from within SQL?
Yes: we can use the stored procedure sp_OACreate to initiate the external COM object from the T-SQL.
What sp_MSforeachtable does ?
You can use sp_MSforeachtable undocumented stored procedure to rebuild all indexes in your database.
Try to schedule it to execute during CPU idle time and slow production periods.
What will you do if you lost rights of your SQL Server instance?
We can use the below options:
- Dedicated Administrator Connection
- BUILIN\Administrator Group (Incase its rights are not revoked)
- Final Option is to change the registry value
- You can change authentication mode via registry
What is SQL Injection?
SQL Injection is developed where unhandled\unexpected SQL commands are passed to SQL Server in a malicious manner. It is a problem because unknowingly data can be stolen, deleted, updated, inserted or corrupted.
What is CPU Affinity?
CPU Affinity sometimes refers as CPU pinning enables a particular process or a thread to use one or more Central Processing Unit, it depends upon the number of CPU configured in your system. It enables particular thread or process to use only configured CPU rather than any CPU that’s configured in your Operating system.
As a part of your job, what are the DBCC commands that you commonly use for database maintenance?
- DBCC CACHESTATS – Displays information about the objects currently in the buffer cache.
- DBCC CHECKDB – This will check the allocation of all pages in the database as well as check for any integrity issues.
- DBCC CHECKTABLE – This will check the allocation of all pages for a specific table or index as well as check for any integrity issues.
- DBCC DBREINDEX – This command will reindex your table. If the indexname is left out then all indexes are rebuilt. If the fillfactor is set to 0 then this will use the original fillfactor when the table was created.
- DBCC PROCCACHE – This command will show you information about the procedure cache and how much is being used. Spotlight will also show you this same information.
- DBCC MEMORYSTATUS – Displays how the SQL Server buffer cache is divided up, including buffer activity.
- DBCC SHOWCONTIG – This command gives you information about how much space is used for a table and indexes. Information provided includes number of pages used as well as how fragmented the data is in the database.
- DBCC SHOW_STATISTICS – This will show how statistics are laid out for an index. You can see how distributed the data is and whether the index is really a good candidate or not.
- DBCC SHRINKFILE – This will allow you to shrink one of the database files. This is equivalent to doing a database shrink, but you can specify what file and the size to shrink it to. Use the sp_helpdb command along with the database name to see the actual file names used.
- DBCC SQLPERF – This command will show you much of the transaction logs are being used.
- DBCC TRACEON – This command will turn on a trace flag to capture events in the error log. Trace Flag 1204 captures Deadlock information.
- DBCC TRACEOFF – This command turns off a trace flag.
