Category: SQL SERVER

No comments

SQL Server – Installing SQL Server 2016 Developer Edition (Free) with Screenshots

  By ,

NOTE: SQL Server Management Studio is not part of the SQL Server 2016 media anymore Please download it here

1. Download SQL Server 2016 Developer Edition. I downloaded SQL Server 2016 Developer Edition using my MSDN account.

Alternatively you may go this link to get the SQL Server 2016 Evaluation.

2. Right-click then Run SQL Server 2016 setup as administrator.

 

3. Choose Installation on the left-pane then click New SQL Server stand-alone installation… (the first choice).

4. I’m installing a Developer edition (free) hence there is no need to enter a product key. Click Next.

5. Accept the license terms then click Next.

6. I opted not to tick User Microsoft Updates. Click Next.

7.  Ignore the warning about Windows Firewall. This means that the Windows Firewall for Database Engine Access is not yet configured. We can do that later. Click Next.

8. Select the necessary features that you need. You can read the descrption in the feature description box.

9. Choose Default instance if you are not going to create multiple instances of SQL Server on the same machine. Otherwise go for Named Instance. Click Next.

10. Enter the service accounts that you will use to run SQL Services. I’ve set the Startup Type to Automatic for all components.

11. Go to the Collation tab. I’ve accepted the defaults because this is exactly what our application needs. Some application require you to choose a specific collation. You can click Customize to change it. Click Next.

12. I’ve chosen Mixed Mode. This is highly recommended so that there is an additional built in SA accout with a separate user name and password. To add users such as the one I’m using, click Add Current User. to allow account to log into SQL Server.

13. I left the Data Directories to defaults. Though it can be changed if you have a multiple disk environment. Additionally, for better performance, you may want to separate out where different parts of the DBMS go.

14. Configure multiple tempDB data files. This is to increase the I/O throughput to tempdb. When you create multiple data files, they will all be in the primary filegroup and SQL Server uses a proportional fill algorithm to determine which file to use for each request to create an object. If all the files are exactly the same size, then SQL Server uses the files in a “round robin” fashion, spreading the load equally across the files. As a general guideline, create one data file for each CPU on the server and then adjust the number of files up or down as necessary.

15. I did not enable FILESTREAM but you may want to if you plan to explore large file types such as BLOB. Click Next.

16. If you have selected Analysis Services component, you will be shown with the following screen. Select the Multidimensional and Data Mining Mode. Click Add Current User. Click Next.

17. Leave the Data Directories to default. Click Next.

18. If you have selected Reporting Services component, you will be presented with the following screen. I chose Install only. We will configure it later. Click Next.

19. Review selected features and click Install.

20. Once completed. Click Close.

 

 

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL Server – Troubleshoot a Transaction Log Full Transaction Error (Msg 9002)

  By ,

I encountered the error below.

Msg 9002, Level 17, State 2, Line 1
The transaction log for database ‘DBName’ is full due to ‘LOG_BACKUP’.

The following are the corrective actions that can be done.

  • Take a log backup (The #1 cause of full transaction log is the lack of backups)
  • Kill a long-running transaction
  • Alter database mirroring, availability groups, or replication to allow the transaction log to clear
  • Either manually grow the existing log file(s) or add another log file (temporarily)
  • As a last resort, switch to SIMPLE recovery model

Execute the script below to know what is the reason why the log cannot be cleared.

SELECT [log_reuse_wait_desc]
	FROM [master].[sys].[databases]
	WHERE [name] = N'DB_Name';
GO

Some examples of why the log is not clearing are LOG_BACKUP, DATABASE_MIRRORING, NOTHING.

You may check the full list in Books Online at http://bit.ly/PoX2xe

 

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL Server – MAXIMUMERRORCOUNTREACHED Error (Maintenance Plan)

  By ,

I setup a maintenance plan to backup my databases. I ran it  and I hit the error below.

The problem is because there are a couple of databases that are having an error -transaction log full due to CHECKPOINT (This is a different story). Hence, I’m not able to backup these databases. If you look at the log closely, you will notice the errors (refer to the screenshot below).

So what I did was to deselect these databases to make the job run successfully.

Alternatively though, you increase the maximum error count to make the job go thru successfully despite hitting errors.

  1. Right-click on the Backup Task then go to Properties.

2. Increase the number of the “MaximumErrorCount” parameter.

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
2 Comments

SQL Server – Reinitializing a Single Article In Transaction Replication

  By ,

In this blog post, I will list down the steps on how to re-initialize just a single article in transaction replication

1.  Turn off @allow_anonymous and @immediate_sync on the publication.

use PUBLICATION_DB_NAME
go
EXEC sp_changepublication
@publication = 'Replication_Test',
@property = N'allow_anonymous',
@value = 'false'
GO
               
EXEC sp_changepublication
@publication = 'Replication_Test',
@property = N'immediate_sync',
@value = 'false'
GO

The reason we have to disable @immediate_sync is that everytime you add a new article, and if @immediate_sync is enabled, it will cause the entire snapshot to be applied. Our objective is to only apply a particular article.

2.  Add new article.

EXEC sp_addarticle
@publication = 'Replication_Test',
@article = 'REPL_TABLE',
@source_object = 'REPL_TABLE',
@force_invalidate_snapshot = 1

3.  Refresh the subscription

EXEC sp_refreshsubscriptions @publication = 'Replication_Test'
GO

4.  Check the current snapshot agent history.

use distribution
go
select * from dbo.MSsnapshot_history

5.  Start Snapshot agent.

EXEC sp_startpublication_snapshot @publication = 'Replication_Test';
GO

6.  Check the Snapshot Agent history again. You should see a snapshot generated only for the newly added article/s.

7.  Turn ON @allow_anonymous and @immediate_sync on the publication.

use PUBLICATION_DB_NAME
go

EXEC sp_changepublication
@publication = 'Replication_Test',
@property = N'immediate_sync',
@value = 'true'
GO


EXEC sp_changepublication
@publication = 'Replication_Test',
@property = N'allow_anonymous',
@value = 'true'
GO

This is how you can re-initialize only the required articles.

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL Server – Check The Port Configured in SQL Server

  By ,

Use the script below to find the tcp port number that the SQL Server is listening on.

SELECT local_tcp_port
FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID
GO

 

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL Server – Get Backupset Records

  By ,

The script below retrieves the backupset records.  A backup set contains the backup from a single, successful backup operation. RESTORE, RESTORE FILELISTONLY, RESTORE HEADERONLY, and RESTORE VERIFYONLY statements operate on a single backup set within the media set on the specified backup device or devices.

 

USE msdb ;
SELECT 
backup_start_date ,
backup_finish_date,
CAST(ROUND ((backup_size / 1024 / 1024), 0, 1) AS DECIMAL (18,0)) AS Backup_Size_MB ,
recovery_model,
[type]
FROM dbo.backupset
WHERE database_name = 'RBD_DB'
ORDER BY backup_start_date DESC

/*
Backup type. Can be:
D = Database
I = Differential database
L = Log
F = File or filegroup
G =Differential file
P = Partial
Q = Differential partial
Can be NULL.

Reference: https://technet.microsoft.com/en-us/library/ms186299(v=sql.110).aspx
*/

 

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL Server SSRS – Query the Report Server Execution Log

  By ,

Below is the T-SQL script to query the Report Server Execution Log. You may query either the dbo.ExecutionLog2 or dbo.ExecutionLog3 table (same as dbo.ExecutionLog2 with 2 fields renamed: ReportPath has been renamed to ItemPath and ReportAction has been renamed to ItemAction)

Below is the output of the query.

USE ReportServer 
GO

SELECT *
FROM dbo.ExecutionLog3
ORDER BY TimeStart DESC
Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL Server – Get Index Creation Date

  By , ,

There are many T-SQL scripts to get the index creation date but I always use this one.

 DECLARE @filename VARCHAR(500) 
SELECT @filename = CAST(value AS VARCHAR(500)) 
FROM fn_trace_getinfo(DEFAULT) 
WHERE property = 2 
  AND value IS NOT NULL 

-- Go back 4 files since default trace only keeps the last 5 and start from there.
SELECT @filename = substring(@filename, 0, charindex('_', @filename)+1) + convert(varchar, (convert(int, substring(left(@filename, len(@filename)-4), charindex('_', @filename)+1, len(@filename)))-4)) + '.trc'

SELECT 
       gt.EventClass, 
       gt.EventSubClass,
       te.Name AS EventName,
       gt.HostName, 
       gt.StartTime, 
       gt.DatabaseName,
       gt.ObjectName
FROM fn_trace_gettable(@fileName, DEFAULT) gt 
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id 
WHERE EventClass = 46
  and ObjectType = 22601
  and gt.DatabaseName <> 'tempdb'
ORDER BY StartTime desc;

No comments

SQL Server – Export SSRS Report to PDF in Landscape Format

  By ,

By default, your SSRS reports are generated in portrait format. The body of your report will be automatically resized  when you add report items.  When you deploy your reports, it will portray correctly in your browser. But when you export your report to PDF, you will notice that it is in portrait format. It is not presentable and it will have extra pages. You can prevent this from happening  by exporting the PDF in landscape format. To do that, just follow the steps below.

  1. Go to the properties of your report. (Right-click mouse just outside the body of your report). Set the width of the report to the landscape size of your A4 paper: 29.7 cm. Set the height of the report to 21 cm.

 

2. When you export the report, you will see extra blank pages. To avoid that, the                         size of the body should be less or equal to the size of the report margins

Set the width of the body to 26.7 cm                                                                                                   Set the height of the body to 18 cm

So now, when you export your report to PDF, it will be in landscape format.

 

 

 

No comments

SQL Server – Check Database Properties

  By ,

The T-SQL query below can be used to check the properties of your databases.

USE master
GO
SELECT  name AS 'Database_Name' ,
        snapshot_isolation_state AS 'Allow Snapshot Isolation' ,
        is_ansi_null_default_on AS 'ANSI NULL Default' ,
        is_ansi_nulls_on AS 'ANSI NULLS Enabled' ,
        is_ansi_padding_on AS 'ANSI Paddings Enabled' ,
        is_ansi_warnings_on AS 'ANSI Warnings Enabled' ,
        is_arithabort_on AS 'Arithmetic Abort Enabled' ,
        is_auto_close_on AS 'Auto CLOSE' ,
        is_auto_create_stats_on AS 'Auto Create Statistics' ,
        is_auto_shrink_on AS 'Auto Shrink' ,
        is_auto_update_stats_async_on AS 'Auto Update Statistics Asynchronously' ,
        is_auto_update_stats_on AS 'Auto Update Statistics' ,
        is_cursor_close_on_commit_on AS 'Close Cursor on Commit Enabled' ,
        is_concat_null_yields_null_on AS 'Concatenate Null Yields Null' ,
        is_db_chaining_on AS 'Cross-Database Ownership Chaining Enabled' ,
        is_date_correlation_on AS 'Data Correlation Optimization Enabled' ,
        is_read_only AS 'Database Read-Only' ,
        is_local_cursor_default AS 'Default Cursor' ,
        is_encrypted AS 'Encryption Enabled' ,
        is_arithabort_on AS 'Numeric Round-Abort' ,
        page_verify_option_desc AS 'Page Verify' ,
        is_parameterization_forced AS 'Parameterization' ,
        is_quoted_identifier_on AS 'Quoted Identifiers Enabled' ,
        is_read_committed_snapshot_on AS 'Read Committed Snapshot' ,
        is_recursive_triggers_on AS 'Recursive Triggers Enabled' ,
        user_access_desc AS 'Restrict Access' ,
        is_broker_enabled AS 'Service Broker Enabled' ,
        is_trustworthy_on AS 'Trustworthy'
FROM    sys.databases ;
GO