Tag Info

New answers tagged

0

You may have already attempted this, but I must ask: Have you attempted to initialize mirroring via the SSMS GUI? In SSMS on principle server, right-click datbase --> Mirror --> Configure Security. I'm curious if the same error message is generated from the GUI. Can you establish a connection to your mirror server from the principle server via SSMS?


1

I need to decrease the limit SQL server uses to 28 GB and I have set the same to 28GB but it is still taking more. Buffer pool only caters to request which require pages <=8KB for any pages or memory request which require pages >8KB memory would not be allocated by buffer pool but would directly be done by windows API. This memory allocation can ...


1

This totally depends how healthy the server is SQL1,2008R2. You will need to analyse first the usage of Databases, SSIS or SSRS and other parameters hosted on the server. Also, what resources you have in terms of RAM, CPU, Disk(Raid levels) etc. available on the server along with how they are being getting used. It would be a good start if you separate ...


1

SQL Server's memory manager is designed to keep as much data cached in memory as it can in order to speed up access for incoming queries. In order to work successfully with other processes on the machine, SQL Server will release memory when a low- memory notification is sent by Windows. The memory may be committed by the SQL Server process, but it will be ...


0

This can be done. But as far as i know, it will break the replication.You'll have to enable the option and re-initialize the subscriber with a new snapshot. Also, if you have non-clustered indexes with INCLUDED, you'll have to modify the definitions (.IDX files) manually to add INCLUDED to them. You can enable the option where you can replicate the non ...


0

I believe as the SQL Server max memory has not been capped before to max memory it has grabbed all the memory available and not able to release it now, can you try clearing all the cache and see if it works, if not you need to Restart the SQL services to release the memory to OS according to me.


0

How is the maintenance of these two database? The first have a lot of reads and the second have a lot of writes The first database needs update statistic before the process and the second database needs a dbcc dbreindex() to avoid prevent space alocation and page split


1

You will almost always be susceptible to vendor updates when it comes to schema changes and their app, especially when it comes to ERP applications such as SAP Business One or it's bigger brother. The issue is how will their update scripts react to your table being added/modified? Usually it's not a big deal for me but sometimes it could cause massive ...


2

No, you can't fetch scalar values from a cursor row into a table variable. You would have to declare the variables, fetch into them, and then insert: FETCH idCursor INTO @id, @data; INSERT @currentId SELECT @id, @data; However, perhaps it's the case that you don't need a cursor at all. Why are you processing one row at a time? Why not populate the @table ...


2

But why the recompile didn't help? Using OPTION (RECOMPILE) is not magic. It does often produce a better-performing execution plan (at the cost of a full compilation each time) because the query is compiled each time for the specific values of any parameters at the time, and also enables parameter embedding, which may produce further benefits. ...


2

I can confirm that as SQL 2012 SP2 CU7, which has the TLS 1.2 support for SQL 2012 from CU6, you cannot disable TLS 1.0 at the server level and be able to connect to SQL server using an un-encrypted management studio connection on an instance which does not force client encryption. This is on an instance that does not use TDE or other certificates. I will ...


5

Microsoft has recently revealed (without a lot of fanfare) that they will be investing in TLS 1.2 and phasing out SSL. It should be relevant to all editions of SQL Server. Whether this move will affect all existing versions, just 2014 and above, or just 2016, remains to be seen. The quote below seems to imply at least 2014 will be part of the work - and I ...


0

Brent, Your approach is going to depend on what your recovery mode the database is in. This is the MSDN DBCC SHRINKFILE reference for your version of SQL Server. For databases that are in SIMPLE recovery mode you should be able to execute the DBCC SHRINKFILE (N'<logical_file_name_of_the_log>'<Size In MB>) That should be all ...


0

I wrote a query that seems to work. It uses Common Table Expressions, MERGE statements and Analytic functions. However it is only compatible with SQL server 2012+. You can find the gist here: MergeRecordsByValidityDate.sql /* NOTE: Only works w/ SQL Server 2012+ Merging identical records with different validity dates. */ USE [master] IF ...


2

Does anyone know how I can redefine the Log file with an Init size of 200 mb? You have to use ALTER DATABASE [YOUR_DB_NAME] ... MODIFY FILE ... SIZE ALTER DATABASE [your_db_name] SET RECOVERY SIMPLE; CHECKPOINT; -- shrink the log file to 200MB DBCC SHRINKFILE (your_db_name_log,200); --- define the initial size to 200MB as well.. CHANGE it as per ...


1

First of all please note initial size is not actually what it means. Yes you can do it but make sure you don't use SSMS GUI. ALTER DATABASE [db_name] SET RECOVERY SIMPLE; sp_helpdb db_name --get logical log file name CHECKPOINT; DBCC SHRINKFILE (db_name_log,0); ALTER DATABASE [db_name] MODIFY FILE ...


1

The issue here is mostly order. Otherwise you could just do SELECT Name FROM Table UNION ALL SELECT Age... So let's assign some values to order by: --This order is arbitrary, and will order the rows in whatever order they come. WITH NumberedRows AS (SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rownum FROM Table ) --Now do a union all with things ...


0

The problem was an error in my trigger insert code. It was using INNER JOIN's and one was not correct so was not returning any data.


1

My colleague ended up finding an article about what he was referring to: http://www.nigelrivett.net/SQLAdmin/AlterTableProblems.html. After reading this and realizing our year end reporting was coming up, we decided to not make the alterations to the column types and will revisit this in the next couple months. I think after reading the article, I may just ...


2

There is a similar question here. You can achieve this using CROSS APPLY with UNION ALL: select c.x from [mytable] cross apply ( select [Name] union all select CAST([Age] as varchar) union all --all the columns must be of the same type select [Type] union all select [City] ) c (x) where x is not null ; As pointed out by @Andriy M it could be also ...


0

Depending on what version of sql server you are using, you can choose : Server side trace - you can use User Error Message Event with filter on error <> 0, error <> 1, severity <> 10 Extended Events To automate import of server side trace files, you can use WMI alerts Note: There is a performance impact on a busy server when you use ...


2

Have you looked into SQL Server's Ring Buffer, especially the Exception Ring Buffer? From http://thesqldude.com/2012/01/31/sql-server-ring-buffers-and-the-fellowship-of-the-ring/ I got the following query which works great for detecting uncaght exceptions and errors: SELECT CONVERT (varchar(30), GETDATE(), 121) as [RunTime], dateadd (ms, (rbf.[timestamp] ...


0

We have the below script handy when have a scenario to restore multiple tran logs in sequence during an issue from here DECLARE @databaseName sysname DECLARE @backupStartDate datetime DECLARE @backup_set_id_start INT DECLARE @backup_set_id_end INT -- set database to be used SET @databaseName = '<your_database_name_here>' SELECT @backup_set_id_start ...


2

Use dynamic SQL. But join to sys.columns to avoid SQL Injection attacks. DECLARE @qry NVARCHAR(MAX) = 'SELECT ' + STUFF(( SELECT ', ' + QUOTENAME(c.name) FROM Report.InfoColumnOrder r JOIN sys.columns c ON c.name = r.ColumnName AND c.object_id = object_id('dbo.Info') WHERE u.UserId = @user ORDER BY r.SN FOR XML PATH(''), ...


4

I would suggest to use Restore Gene : Automating SQL Server Database Restores - written by Paul Brewer The Restore Gene stored procedure generates the required restore scripts, including the DBCC CHECKDB command, if required. It is a useful tool, by itself; in disaster recovery situations, it can construct a restore script, in seconds. It optionally ...


0

This uses xp_cmdshell to look at the file system for the log files. If you're OK with that.... DECLARE @databaseName AS VARCHAR(100) DECLARE @restoreName AS VARCHAR(100) SET @databaseName = 'OriginalDBName' SET @restoreName = 'RestoredDBName' CREATE TABLE #dirList ( id int identity(1,1), line nvarchar(1000) ) DECLARE @cmdShellStatement ...


3

Please use Database Mail instead of SQL Mail. SQL Mail had security issues on several layers and in SQL 2005 SP2 had 'inbound emails' removed. They then further removed support for it and it is now in the depreciation list. Database Mail is the solution you are looking for. It passes audits easily, has lots of documentation, and is fully supported by ...


6

I get the same error when I do this: DECLARE @foo VARCHAR(32) = CHAR(0); -- 0x0000 SELECT @foo FOR XML PATH, TYPE; So, find all instances of DescFuncao or DescTempoExperiencia (sorry, you don't use table aliasing, so it's impossible to tell which table they come from) where the contents contain CHAR(0), and fix them. For example: UPDATE dbo.whatever ...


3

The line: ...SELECT [CodFuncionario],STUFF(( SELECT ' / ' + CAST([DescFuncao] + '-' + [DescTempoExperiencia] AS VARCHAR(MAX)) Should be: ...SELECT [CodFuncionario],STUFF(( SELECT ' / ' + CAST(replace([DescFuncao] + '-' + [DescTempoExperiencia],char(0),'') AS VARCHAR(MAX))


0

Bearing in mind that PowerShell will be running under the Windows Account used to launch the SQL Agent service... there's a good chance that there's either a PowerShell prompt or a GUI prompt (e.g.: UAC) causing the process to never finish from SQL Agent's point of view. If SQL Server is running under the same Windows Account as SQL Agent then you could try ...


1

I wouldn't expect a copy operation to increase file sizes unless the file size was too small to begin with. SQL Server will first use unallocated space within data files before growing the file. Similarly, if the log file is large enough for the operation, that file shouldn't grow either. Also regarding log space usage, some operations can be minimally ...


1

Be careful with the drop and recreate option: this can leave sys.depends in an odd state and cause problems for cached plans where the ordering or type of columns is changing. You will also need to take steps to maintain any object level permissions, as these will be lost in the DROP and not automatically recreated with the subsequent CREATE. ALTER TABLE ...


6

I recently did this in my organization wherein we wanted to handle a table with billion + rows. All the credit for the idea goes to Aaron Bertrand and is from his blog post Trick Shots : Schema Switch-A-Roo Test below process on a small table and get your self comfortable before doing it in PROD. create 2 schemas fake and shadow with authorization dbo. ...


3

Here is the way I see it. Pros for #1 Because you are using a separate table your production table stays in use until you are done. No locks on it (beyond those needed to read the data). This also goes with what @AaronBertrand said: you can do it piecemeal, test etc You can change column order at need Pros for #2 It is an all or nothing operation. ...


3

Its not possible for the variable name to change by itself, its likely you restored from a different (older? newer?) backup than what you thought. You can check sys.objects for the stored procedure create/modified dates and compare across the 2 databases.


0

I don't have a restored master DB to try this, but running the statement of the view definition (with the references to master. changed to your user db name) should be a step in the right direction. You can get the statement by running sp_helptext [sys.credentials]


1

As Oracle support note "Client / Server Interoperability Support Matrix for Different Oracle Versions" ( Doc ID 207303.1 ), Oracle Client 12.1 does not support connections with Oracle DB servers 10.1 and below. Connections with Oracle DB 10.2 are still supported, but not recommended. The recommended option its to use Oracle 10.2 client.


7

You have to use an ORDER BY. Adding a hint to use a specific index does not guarantee any ordering of the resultset: http://stackoverflow.com/questions/10064532/the-order-of-a-sql-select-statement-without-order-by-clause


-1


1

If it's a named instance well, then, you should have always been using localhost\instance. This is how it's supposed to work. You shouldn't have been able to connect before and I suspect there is a vital detail missing that would explain that. Possible explanations (there may be others): the instance was the only one on the machine, and maybe an alias was ...


3

Can anyone explain the difference between turning off the SQL Browser service and Hiding an instance through the SQL Server Configuration Manager? To hide an instance of SQL Server, you need to set HideInstance flag to YES. When you hide an instance, it will indicate that sql server browser should NOT respond with information about this server ...


1

1.How they talk each other(the two Servers)? From the database engine perspective, both SSRS and SSAS are clients like any other application. These query data from SQL Server using SQL queries. Microsoft just chose to include these components with SQL Server licensing and media. 2.Where the cube for SSAS stored? On the server with SSAS installed ...


0

I spent a while in a test environment trying to reproduce your issue and have had some success. If I set up Log Shipping in its most basic form, and in such a way as to mimic your situation, everything appears to be working. However, I have found one way to configure the environment that results in messages similar to yours... Message 1: Message 2: ...


2

I have upgraded from SQL Server 2003 to SQL Server 2008 R2 because my database was 4G and needed extra space. There is no SQL Server 2003 .. I believe you meant SQL Server 2005. I though SQL Server 2008 R2 has a limit of 10Gigs hence I would have 6 Gigs free, however I only got 1Gig free... Space Available : Displays the amount of available space ...


0

In addition to possible drive failures, check the state of your RAID subsystem. We saw something similar and it turned out the battery on the RAID controller failed so there was no write cache available - all writes had to go directly to disk. One side note - we could feel the system pause while RDC'ing into it.


1

Does .bak file also contain backup of ldf file? No it does not contains backup of ldf file. It might contain some information not the complete backup. A full or differential backup contains enough log to be able to recover the database to a transactionally-consistent view of the database at the time the data-reading portion of the backup finished (or ...


0

Figured out the problem. Even though the user I was adding to the Linked Server security section had permissions into the target database, the user I was logged into the SQL Server instance at the moment didn't. Since the error message is incredibly unhelpful (it doesn't say which user failed to log in), I discovered this entirely by accident.


-1

Because i am not allowed to comment i write this here. To install net framework 3.5 you need to be connected to internet. Im assuming you are ? For more info check this link: https://support.microsoft.com/en-us/kb/2734782


1

You can do this using PowerShell fairly quickly. If you mount the ISO or insert the media for the version of Window Server 2012 you are running, then use the following commands in an PowerShell prompt (elevated privileges required so open it "As administrator": # have to import the module for the cmdlets Import-Module servermanager # you can verify it is ...


1

I have used option 2 from this article many times to fix this issue. Basically insert the windows install disk, enable the .net 3 feature but select the option to 'specify an alternate path' at the end of the wizard. The different path should be D:\sources\sxs (if the disc is in your D drive)



Top 50 recent answers are included