We had some SAN issues and we dont have Transaction Log files for some databases.. The drive which was holding this Tlog files were missing.. How to bring back databases.
View 1 RepliesHi,
I'm trying to move the transaction logs of my databases to a different drive (for fault tolerance). I can create a second transaction log file for each database via Enterprise Manager but I have 2 questions:
1) If two transaction log files exist for a database which one does it use ?
2) How do I force SQL to use the new transaction log file ? (so I can delete old)
Thanks,
Tim
The MDF and LDF files are placed in SSD drive and tempdb files are placed in HDD drive. Snapshot isolation is enabled on the database. When a script is executed to insert data with NULL value to a table which has NOT NULL column, the transaction fails and then a log undo happens which fails and takes the database to suspect mode.
But when the MDF and LDF files are placed in HDD drive all this do not happen. The transaction just fails.
Currently we has a database of size about 300G. Because our backup system failed some time past we were left with a transaction log file which grew to about 160G. However our backups are working again and everything is working fine. My understanding is that now the transaction log file is practically empty but the capacity remains at 160G.
When you delete records the deleted transactions are going to get logged to the transaction file. My understanding is when a backup is done these transactions get discarded out of the transaction file.
could I make use of this relatively large transaction file and start deleting transactions without out actually adding to the transaction file size.
The plan is to delete records from logging tables that are not referenced to by any other table without this increasing the transaction log file.For example over a period of a few weeks we can delete a chunk of records from a table. Then after it has completed a backup we can delete another chunk of records out of this table until we have got the table down to the records that we now need.Will this work?
I am able to run SSIS packages as SQL Server Agent jobs with a Control Flow items "File system task", if I move a file (test.txt) from a drive (c on the server (where SQL Agent jobs run) to a subdirectory on the same drive. But, if I try to move a file on a network drive, the package fail.
What I can do to solve this issue.
Bye!
Daniel
Hi,
I have an SQL cluster on Windows 2003 with 2 drives (1 for dbs and 1 for logs). Both of these drives are accessible from Windows explorer.
In Enterprise Manager only the db drive appears as a location to house dbs and logs.
I can't work out how to get the second drive to appear and adding the path manual doesn't work as I end up with the other drive letter appending to the front eg "y:z:logs"
Has anyone seen this or have any ideas how to fix it?
Help much appreciated.
Wierd issue of a missing master database - wierd because I would have thought this was a newbie topic but I've found nothing for it. I googled and had a 'decent' look through this forum and only found a bunch of topics on 'how to restore master database'.
I wouldn't have thought I need to restore the master database because my SSMS works fine and I can query the master database. I can also see it in the drop down list of available databases in the Query Designer toolbar. The problem is just that I can't see it in the list of databases. I can see all the other databases I've created, and I can see the master database in the DATA folder. But not in the SSMS.
This is SQL Server 2008R2. On the SQL Server Configuration I'm unable to see SQL Server Browser. But when I check the services I can see the SQL Browser running.
Under SQL Server Configuration Manager > SQL Server Services
1. Full text
2. SQL Server (Default)
3. SQL Agent (Default)
But I'm unable to see the SQL Browser services here. What is the remedy?
I have a table of languages, identified by a lang_id column as follows:
LANG_IDLANG_NAME
deDeutsche
enEnglish
plPolski
trTurkish
czCzech
I also have a RESOURCES table of phrases (for translation purposes) similar to this:
res_id res_lang res_phrase
AccessDenied en Access Denied
For some rows in the resources table I do not have all language codes present so am missing some translations for a given res_id.My question is what query can I use to determine the RESOURCE.RES_IDs for which I do not have a translation for.
For example I might have a de, en, cz translation for a phrase but not a pl phrase and I need to identofy those rows in order that I can obtain translations for the missing RESOURCE rows.
We have a situation where SSRS reports are using Testuser account to run the SSRS reports for every user who has access to these reports.Now developers need the password for this login Testuser to proceed with their ssrs reports development, but its sort of mess that no one has password ever saved.So in case is there a way it can be retrieved or what should be an alternative if password cannot be retrieved for that login, as i believe changing the password will break the reports using the current password?
View 7 Replies View RelatedI am trying to move a log file from one drive to another.
What I have done is add another file to my file group. So now my log has a file on the 'e' drive and one on the 'f' drive. I now want to remove the file on the 'e' drive. I have emptied the file on the 'e' drive. When doing the command:
ALTER DATABASE Uniprodruntime
REMOVE FILE m_rk_runtime_log
I get the following error message..
Server: Msg 5020, Level 16, State 1, Line 1
The primary data or log file cannot be removed from a database.
I have also gone into enterprise manager and tried to delete the file and it does nothing.
Has anyone run into this?
Hi All,
I've been trying to find the answer but been unable to. My question is it possible to create a SQL Server (2005) database in a usb2 drive? I have a large usb drive that i would like to store my database into instead of my local drive which is not that big.
Thanks
I have Full database backup upto previous day and transaction logfile of Today transaction. my database has crashed. I have restored previous day's Full backup. I have faced difficulty to restore today's transaction from today's transaction log. What are the steps to restore full database back and one day's transaction log file. Note: there is no differential database backup and transaction backup.
View 8 Replies View Related
I downloaded and performed a full install of SQL Server Express 2008 Nov CTP. It was installed on a fresh installation of Windows Vista Enterprise x64.
During setup, I checked the 'Client Tools' box which has the description:
"Includes management and development tools: SQL Server Management Studio, SQL Server Configuration Manager, SQL Server Profiler, and Database Engine Tuning Advisor"
The installation ran fine with no errors. After install I looked for the Management Studio but couldnt find it anywhere. No SQL Server Profiler or Database Tuning Advisor either. The only tool that appears to have installed is the SQL Server Configuration Manager.
If I check my Start Menu, the only 2008 items that exist are
Microsoft SQL Server 2008 >
Configuration Tools >
SQL Server Configuration Manager
SQL Server Error and Usage Reporting
SQL Server Installation Center
I checked the summary log and everythign PASSED:
Package results:
Passed
Execution statistics:
Exit code: 0x00000000
Exit message: Passed
Package start time: 12/13/2007 14:05:39
Package end time: 12/13/2007 14:09:06
Package install location: c:Program Files (x86)Microsoft SQL Server100Setup Bootstrap
Package initial action: Install
Media source location: Install
Media version: 10.0.1075.23
Machine properties:
Machine name: xxx
Machine processor count: 2
OS version: Windows Vista
OS service pack:
OS language: English (United States)
OS architecture: x64
Process architecture: 32 Bit
Properties provided in package.xml file:
LegalProductName: SQL Server Database Services 2008
Description: SQL Server Database Services 2008
Details: Install for SQL Server Database Services 2008
ProductName: SQL2008
Version: 10
SPLevel: 0
KBArticle: KB876234
KBArticleHyperlink: http://support.microsoft.com/?kbid=876234
Command line arguments provided:
ACTION: Install
INSTANCEIDSUFFIX:
Product features discovered:
Product: SQL2005
Product: SQL2008
Feature status after execution:
Database Engine Services: Passed
Replication: Passed
Client Tools: Passed
Windows Installer logs generated during execution:
C:Program Files (x86)Microsoft SQL Server100Setup BootstrapLog20071213_1405T60-l3ac207_20071213_1405_Snac_Cpu64_1.log
C:Program Files (x86)Microsoft SQL Server100Setup BootstrapLog20071213_1405T60-l3ac207_20071213_1405_Msxml6_Cpu64_1.log
C:Program Files (x86)Microsoft SQL Server100Setup BootstrapLog20071213_1405T60-l3ac207_20071213_1405_Tools_Cpu32_1.log
C:Program Files (x86)Microsoft SQL Server100Setup BootstrapLog20071213_1405T60-l3ac207_20071213_1405_SqlWriter_Cpu64_1.log
C:Program Files (x86)Microsoft SQL Server100Setup BootstrapLog20071213_1405T60-l3ac207_20071213_1405_SqlBrowser_Cpu32_1.log
C:Program Files (x86)Microsoft SQL Server100Setup BootstrapLog20071213_1405T60-l3ac207_20071213_1405_Sql_Cpu32_1.log
What is the issue here?
Hi
I am having an Access database on a shared network drive which has read/write access rights on the that shared network drive.
When I try to Access data through the linked server it gives me gives me a message box saying you do not have permissions to view the data.
Also if i try to use xp_cmdshell to copy over the mdb file to my local drive it say 'Access denied'
But when I copy (through command prompt) the same file to another network drive or my local drive where I have full control the linked server can connect sucessfully.
The problem is the i cannot have 'full control' permissions on shared drive where my database resides.
Has anybody encountered this problem....
Any help will be greatly appreciated.
Urgent
Puru
Hi,
Just got a new box and want to fully optimize the server. I was contemplating where to put the transaction log? I have 2 drives:
Drive C: 30 Gig(Operating System)
Drive E: 180 Gig(Database file)
I'm planning to build the database in Drive E:, but am leaning to put the transaction log in drive C:. Do you think it's a good move? I want to separate the log and data files, but Drive C: is host the operating system. Do you think it would be wise to partition drive C: and put the operating system on one partition and the Transaction log on the other? Does it enhance performance in anyway?
Thank You!
Joe
I received alerts from a couple of the production servers last night stating that the log file is running out of space. So, I took some log backups and shrunk those files. However, I would like to find out what made the log grow like that. After all, I have t-log backups scheduled every 30 minutes.Is there a way I can find out the reason behind the log growth?
View 9 Replies View RelatedHello guys and girls. I have installed SQL Server 2005 Standard Edition and I have specified that the databases should be created on the K: drive. This is okay but now I need to move all the transaction log files (.ldf) to the L: drive. I have already changed the default location for the log files to point to the L: drive and the new databases that were created after the installation have their transaction log file correctly in the L: drive but now I need to move transaction log files for the master, model, temp ... databases. How can this be done? And are there any gotchas?
View 11 Replies View RelatedI am confused about save transaction in the below scenario :
begin transaction
save transaction t1
delete from #t1
save transaction t2
begin try
delete from #t2
[Code] ....
If there is error after delete #t2 , transaction t1 is rolled back. But i am not able to understand why i am getting error in the statement 'rollback transaction t2' . I am getting error as 'Cannot roll back t2. No transaction or savepoint of that name was found.'. but save point t2 is mentioned in the code.
Currently, we are on SQL2008R2 EE SP1 CU13 across the board.
We are planning on upgrading the primary SQL server to SP3.
Question: Will transactional replication continue to work properly even if the Publisher is upgraded to SP3 but the subscribers remain at SP1 CU13 ?
I am trying to reorganise the log files on a server, (long story short they are fragmented so I want to shrink and reset the initial size and growth) and I am unable to shrink them. When I run the following:
use test
DBCC SHRINKFILE(test_log, TRUNCATEONLY)
--or
use
DBCC SHRINKFILE(test_log,2, TRUNCATEONLY)
I get the following message:
Msg 8985, Level 16, State 1, Line 1
Could not locate file 'test_log' for database 'test' in sys.database_files. The file either does not exist, or was dropped.
I get this message for every database on the server. I got the logical name of the file using sp_helpfile and have checked it against sys.masterfiles, sys.database_files and sys.sysaltfiles, all match up and confirm the name 'test_log'.
I rebooted the server last night and was able to shrink the first couple of .ldf's I tried so I presumed it was fixed. This morning when I try again i get the sanme error, I don't see anything in the SQL server or system logs that indicates a change.
I am able to add new log files and remove log files, however if I add a new log file (test_log2) and then try and truncate that file I get the same error.
Having a lot of problems with backup device creating backups with a new transaction log for each day. This is causing the backups to grow way to fast. Seems to be random with our clients. Created new device backups but getting same problem. A manual backup selecting overwrite all existing backup sets will fix it. But starts the cycle all over again.
View 9 Replies View RelatedI am altering a table ( changing the data type to varchar (8000) from nvarchar (1500) ) with 352929 rows. I get the transaction log full error.
The database is in FULL RECOVERY model. I changed the recovery model to SIMPLE and performed the alter but I still run into error.
I have two tables one list changes of hospital ward and one lists changes of consultant doctor. These can change independently ie a ward change can occur without a consultant change and vice versa. I want to summarise these changes to give the status at each date_serial value.
create table #temp_ward_stay(date_serial bigint,ward_id varchar(10))
;
insert into #temp_ward_stay
values
(201501021200,'W1'),(201501031201,'W2'),(201501091200,'W3'),(201501161200,'W4'),(201501161201,'W5')(201501271200,'W3'),(201502101200,'W5')
;
create table #temp_consultant_episode(
date_serial bigint,
consultant_id varchar(10))
;
insert into #temp_consultant_episode values
(201501021200,'C1'),(201501031200,'C2'),(201501031201,'C3'),(201501091201,'C4'),
(201501121200,'C3'),(201501301200,'C6'),(201502111200,'C6'),(201502111201,'C7')
;
If I use:
select date_serial,consultant_id,null as ward_id from #temp_consultant_episode
union
select date_serial,null as consultant_id,ward_id from #temp_ward_stay
I get:
date_serial ward_id consultant_id
-------------------- ---------- -------------
201501021200 NULL C1
201501021200 W1 NULL
201501031200 NULL C2
201501031201 NULL C3
201501031201 W2 NULL
201501091200 W3 NULL
201501091201 NULL C4
201501121200 NULL C3
201501161200 W4 NULL
[code].....
whereas what I actually want is:
date_serialward_idconsultant_id
201501021200W1C1
201501031200W1C2
201501031201W2C3
201501091200W3C3
201501091201W3C4
201501121200W3C3
201501161200W4C3
201501161201W5C3
[code].....
I can see how I could remove the nulls where the date_serial values are the same:
select ce.date_serial,ward_id,consultant_id from #temp_consultant_episode ce
join #temp_ward_stay ws
on ce.date_serial=ws.date_serial
but I can't see how to move forward from here.
Historically I've always written a VB script to copy a file from a sharepoint library. I don't like this method because I have to input a username & password in the script and maintain a config file.
Yesterday I was playing around with using a file system task. The sharepoint file has a UNC path so why not? I created a simple test package with a single file system task that copies the sharepoint file (addressed via UNC) to another network location. Package runs fine locally.
I try running on our utility server but am getting a "The file name [SHAREPOINT UNC PATH] specified in the connection was not valid" error. Package is running with a proxy on the server and the proxy account has the same permissions to the sharepoint site (so far as I can tell) as me.
one of my database data file is 100 GB and the log file is 500 GB.DB is in full recovery model and the transaction logs happen once in 6 hours.Even then, the Database log file isn't reducing in size.
View 9 Replies View Relatedi would like to know it's possible to find all transaction(insert, delete,update) on a database for a day. if yes what can i do.
View 2 Replies View RelatedWith the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False
'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i
' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
I am trying to send a csv file with 15000 records via the database mail in SQL Server 2014. The problem is that when I open my email the csv only contains 209 records. I have tried the same thing in SQL Server 2012 and it works as expected - it sends the 15000 records in the csv.
I have tested this on several sql servers with 2014 edition on them, and I have the same issue on all of them. The query breaks off at different points on each sever - for example one of them breaks off at 209 records as i said above, another one at 307. The last record always gets truncated at the same place. The csv attachment size it's about 64 kb - which is well below the 4MB limit i've configured the database Maximum File Size bytes parameter.
What i am doing basically is creating a job that is meant to execute a stored procedure and send the results in a csv in an email. The stored procedure is something like:
select col1, col2, col3
from table A1
where col1 > 1000 order by col1
can anyone please point me in the right direction as to how to go about rebuilding the transaction log files on a database?
here is the scenario:
1 - transaction log drive failed and transaction log file is basically gone.
2 - database file is fine.
3 - backup is too old or for all intents and purposes non-existent.
the server initially showed the database as suspect. the database was detached and an attempt was made to attach it with a recovered copy of the transaction log file but apparently it was too corrupted and the server didn't like it.
any suggestions would be greatly appreciated.
by the way, after looking at some of the posts here, i tried ApexSQL Log and Red-Gate Rescue bundle but these tools seem to require a database to at least show up on the database list, even if it is suspect.
the database doesn't even show up on the list since it was detached.
thanks in advance.
i have transactional replication setup. recently i found that some of the new records in the publisher are not replicated to subscriber, but the replication is going on as normal with most of the record replicated.
the only this happen is user stop and restart the synchronization.
why this happen, how to find all the records which are not replicated and how to replicate to subscriber now.
Hi All,
I want to import a txt file data to a sql server database table, to do this i used sql server import and export wizard. In this when we choose a Data Source, the option Flat file source is not coming up in the combo box in the wizard.
I am using sql server 2005, Management Studio to do this.
steps 1. right click on the database --> all tasks --> import data --> sql server import export wizard --> choose data source dialog box....
please help me.
thanks in advance.
I have a transaction number in my mapping table. I have a matching transaction number in my PDHist table. Sometimes I have matching transaction numbers in my PD table, but not always. This is causing no records to be returned. I have a One to Many relationship between my mapping table and both PD and PDHist.
Also, I need to check for nulls in my foreign exchange table.I can’t post the SQL because this is a classified project. However, it should be something like this, I think.
IIf(IsNull([Redem]![FX Rate]),([PDHist]![Remaining Balance]+[PD]![Closing Balance(TC)]).
The addition isn’t working. I think with a small push I can get this straightened out.